/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Database.DBConnection;
import Database.PropertiesManager;
import Model.Espaco;
import Model.Reserva;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;


public class ReservaDAO {
    private static HashMap dados;
    
    static{
        try{
    dados = new PropertiesManager("sql.properties").readPropertiesFile();
        } catch (IOException ioe){
            System.out.println(ioe);
        }
    }
    
    
    public static boolean inserir(int id_espaco,Date data_reserva, Date data_inicio, Date data_fim, int presenca, String login) throws SQLException, ClassNotFoundException, IOException{
        Connection conexao = DBConnection.getInstance();
              
               
     
       
        
        //Se o espaco nao existe retorna falso
        Espaco e = EspacoDAO.PesquisarById(id_espaco);
        if(e != null){
            return false;
        }
        
        Reserva reserva = new Reserva(0, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
        
        
        String sql = loadSQL("Insert.Espaco");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        
        pstmt.setInt(1, reserva.getId_espaco());
        
        if(reserva.getData_reserva() != null){
                pstmt.setDate(2, new java.sql.Date(reserva.getData_reserva().getTime()));
            }
            else{
                pstmt.setDate(2, null);
            }
        
        if(reserva.getData_inicio() != null){
                pstmt.setDate(3, new java.sql.Date(reserva.getData_inicio().getTime()));
            }
            else{
                pstmt.setDate(3, null);
            }
        if(reserva.getData_fim() != null){
                pstmt.setDate(4, new java.sql.Date(reserva.getData_fim().getTime()));
            }
            else{
                pstmt.setDate(4, null);
            }
        
        pstmt.setInt(5, reserva.getPresenca());
        pstmt.setString(6, reserva.getLogin());
        pstmt.executeUpdate(); // tbm funciona com pstmt.execute();
        return true;
    } 
    
    public static boolean editar(int id_reserva,int id_espaco,Date data_reserva, Date data_inicio, Date data_fim, int presenca, String login) throws SQLException, ClassNotFoundException, IOException{
        Connection conexao = DBConnection.getInstance();
                
       
        
        Espaco e = EspacoDAO.PesquisarById(id_espaco);
        if(e == null){
            return false;
        }
        
        Reserva reserva = new Reserva(id_reserva, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
        
        
        String sql = loadSQL("Update.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        
       pstmt.setInt(1, reserva.getId_espaco());
        
        if(reserva.getData_reserva() != null){
                pstmt.setDate(2, new java.sql.Date(reserva.getData_reserva().getTime()));
            }
            else{
                pstmt.setDate(2, null);
            }
        
        if(reserva.getData_inicio() != null){
                pstmt.setDate(3, new java.sql.Date(reserva.getData_inicio().getTime()));
            }
            else{
                pstmt.setDate(3, null);
            }
        if(reserva.getData_fim() != null){
                pstmt.setDate(4, new java.sql.Date(reserva.getData_fim().getTime()));
            }
            else{
                pstmt.setDate(4, null);
            }
        
        pstmt.setInt(5, reserva.getPresenca());
        pstmt.setString(6, reserva.getLogin());
        pstmt.setInt(7, reserva.getId_reserva());
        pstmt.executeUpdate(); // tbm funciona com pstmt.execute();
        return true;

      
    } 
    
    public static Reserva PesquisarById(int id_reserva) throws SQLException, ClassNotFoundException, IOException {
        Connection conexao = DBConnection.getInstance();
        String sql = loadSQL("SelectById.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        pstmt.setInt(1, id_reserva);
        ResultSet rs = pstmt.executeQuery();
        Reserva reserva = null;
        if(rs.next()){
            int id_espaco = rs.getInt("ID_ESPACO");
            Date data_reserva = rs.getDate("DATA_RESERVA");
            Date data_inicio = rs.getDate("DATA_INICIO");
            Date data_fim = rs.getDate("DATA_FIM");
            int presenca = rs.getInt("PRESENCA");
            String login = rs.getString("LOGIN");
           
            //pesquisar endereco
            reserva = new Reserva(id_reserva, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
            
        }
        return reserva;
        
    }
    
    public static ArrayList<Reserva> PesquisarByEspaco(int id_espaco) throws SQLException, ClassNotFoundException, IOException{
        Connection conexao = DBConnection.getInstance();
        String sql = loadSQL("SelectByIdEspaco.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        pstmt.setInt(1, id_espaco);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Reserva> reservas = new ArrayList<Reserva>();
        Reserva reserva =null;
        
        
        
        while(rs.next()){
            int id_reserva = rs.getInt("ID_RESERVA");
            Date data_reserva = rs.getDate("DATA_RESERVA");
            Date data_inicio = rs.getDate("DATA_INICIO");
            Date data_fim = rs.getDate("DATA_FIM");
            int presenca = rs.getInt("PRESENCA");
            String login = rs.getString("LOGIN");
            
            //pesquisar endereco
            reserva = new Reserva(id_reserva, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
            reservas.add(reserva);
        }
        return reservas;
    }
    
    
    public static ArrayList<Reserva> PesquisarByDate(Date data) throws SQLException, ClassNotFoundException, IOException{
        Connection conexao = DBConnection.getInstance();
        String sql = loadSQL("SelectByDate.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        
        if(data != null){
                pstmt.setDate(1, new java.sql.Date(data.getTime()));
            }
            else{
                pstmt.setDate(1, null);
            }
        
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Reserva> reservas = new ArrayList<Reserva>();
        Reserva reserva;
        while(rs.next()){
            
            
            int id_reserva = rs.getInt("ID_RESERVA");
            int id_espaco = rs.getInt("ID_ESPACO");
            Date data_reserva = rs.getDate("DATA_RESERVA");
            Date data_inicio = rs.getDate("DATA_INICIO");
            Date data_fim = rs.getDate("DATA_FIM");
            int presenca = rs.getInt("PRESENCA");
            String login = rs.getString("LOGIN");
            
            //pesquisar endereco
            reserva = new Reserva(id_reserva, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
            reservas.add(reserva);
        }
        return reservas;
    }
    
    public static ArrayList<Reserva> Pesquisar() throws SQLException, ClassNotFoundException, IOException {
        Connection conexao = DBConnection.getInstance();
        String sql = loadSQL("Select.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Reserva> reservas = new ArrayList<Reserva>();
        Reserva reserva;
        while(rs.next()){
            
            
            int id_reserva = rs.getInt("ID_RESERVA");
            int id_espaco = rs.getInt("ID_ESPACO");
            Date data_reserva = rs.getDate("DATA_RESERVA");
            Date data_inicio = rs.getDate("DATA_INICIO");
            Date data_fim = rs.getDate("DATA_FIM");
            int presenca = rs.getInt("PRESENCA");
            String login = rs.getString("LOGIN");
            
            //pesquisar endereco
            reserva = new Reserva(id_reserva, id_espaco, data_reserva, data_inicio, data_fim, presenca, login);
            reservas.add(reserva);
        }
        return reservas;
    }
    
    public static boolean removeReserva(int id_reserva) throws SQLException, ClassNotFoundException, IOException{
        Connection conexao = DBConnection.getInstance();
             
        
        String sql = loadSQL("Delete.Reserva");
        PreparedStatement pstmt = conexao.prepareStatement(sql);
        pstmt.setInt(1, id_reserva);
        pstmt.execute();
        
        return true;
    }
    
    private static String loadSQL(String key){
        String sql=null;
        sql = (String) dados.get(key);
        return sql;
    }
    
}
